package db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by dllo on 17/4/13.
 */

public class RecordOperationAndroid implements TableRecordOperation {

    private SQLiteOpenHelper mHelper;

    public RecordOperationAndroid(SQLiteOpenHelper helper) {
        mHelper = helper;
    }

    //增加加油记录表
    @Override
    public void addRecord(RecordEntity record) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        if (record.getRecordId() != 0) {
            values.put(BearSQLiteHelper.RECORD_ID, record.getRecordId());
        }
        values.put(BearSQLiteHelper.DATE, record.getDate());
        values.put(BearSQLiteHelper.ODOMETER, record.getOdometer());
        values.put(BearSQLiteHelper.PRICE, record.getPrice());
        values.put(BearSQLiteHelper.TOTAL, record.getYuan());
        values.put(BearSQLiteHelper.TYPE, record.getType());
        values.put(BearSQLiteHelper.GASSUP, record.getGassup());
        values.put(BearSQLiteHelper.REMARK, record.getRemark());
        values.put(BearSQLiteHelper.CARID, record.getCarId());
        values.put(BearSQLiteHelper.FORGET, record.getForget());
        values.put(BearSQLiteHelper.LIGHTON, record.getLightOn());
        values.put(BearSQLiteHelper.STATIONID, record.getStationId());
        db.insert(BearSQLiteHelper.RECORDS_TBL, null, values);
        db.close();
    }

    //删除加油记录表
    @Override
    public void removeRecord(int id) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String whereClause = BearSQLiteHelper._ID + " =?";
        String[] whereArgs = new String[]{String.valueOf(id)};
        db.delete(BearSQLiteHelper.RECORDS_TBL, whereClause, whereArgs);
        db.close();
    }

    //更新加油记录表
    @Override
    public void updateRecord(RecordEntity car) {

        updateRecord(openDatabase(), true, car);

    }

    private void updateRecord(SQLiteDatabase db, boolean isClose, RecordEntity record) {
        ContentValues values = new ContentValues();
        values.put(BearSQLiteHelper.DATE, record.getDate());
        values.put(BearSQLiteHelper.ODOMETER, record.getOdometer());
        values.put(BearSQLiteHelper.PRICE, record.getPrice());
        values.put(BearSQLiteHelper.TOTAL, record.getYuan());
        values.put(BearSQLiteHelper.TYPE, record.getType());
        values.put(BearSQLiteHelper.GASSUP, record.getGassup());
        values.put(BearSQLiteHelper.REMARK, record.getRemark());
        values.put(BearSQLiteHelper.CARID, record.getCarId());
        values.put(BearSQLiteHelper.FORGET, record.getForget());
        values.put(BearSQLiteHelper.LIGHTON, record.getLightOn());
        values.put(BearSQLiteHelper.STATIONID, record.getStationId());
        String whereClause = BearSQLiteHelper._ID + " =?";
        String[] whereArgs = new String[]{String.valueOf(record.getRecordId())};
        db.update(BearSQLiteHelper.RECORDS_TBL, values, whereClause, whereArgs);
        if (isClose) {
            closeDatabase(db);
        }
    }

    //查询被选中的车辆的所有信息
    @Override
    public List<RecordEntity> queryRecord() {
        List<RecordEntity> records = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "SELECT A.*" +
                "FROM records_tbl as A, cars_tbl AS B" +
                "where" +
                "A.carId = B._id" +
                "AND" +
                "B.selected = 1;";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null && cursor.moveToFirst()) {
            int indexId = cursor.getColumnIndex(BearSQLiteHelper.RECORD_ID);
            int indexDate = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexTotal = cursor.getColumnIndex(BearSQLiteHelper.TOTAL);
            int indexType = cursor.getColumnIndex(BearSQLiteHelper.TYPE);
            int indexGassup = cursor.getColumnIndex(BearSQLiteHelper.GASSUP);
            int indexRemark = cursor.getColumnIndex(BearSQLiteHelper.REMARK);
            int indexCarId = cursor.getColumnIndex(BearSQLiteHelper.CARID);
            int indexForget = cursor.getColumnIndex(BearSQLiteHelper.FORGET);
            int indexLightOn = cursor.getColumnIndex(BearSQLiteHelper.LIGHTON);
            int indexStationId = cursor.getColumnIndex(BearSQLiteHelper.STATIONID);
            do {
                int id = cursor.getInt(indexId);
                int date = cursor.getInt(indexDate);
                int odometer = cursor.getInt(indexOdometer);
                float price = cursor.getFloat(indexPrice);
                float yuan = cursor.getFloat(indexTotal);
                int type = cursor.getInt(indexType);
                int gassup = cursor.getInt(indexGassup);
                String remark = cursor.getString(indexRemark);
                int carId = cursor.getInt(indexCarId);
                int forget = cursor.getInt(indexForget);
                int lightOn = cursor.getInt(indexLightOn);
                int stationId = cursor.getInt(indexStationId);
                RecordEntity record = new RecordEntity();
                record.setRecordId(id);
                record.setDate(date);
                record.setOdometer(odometer);
                record.setPrice(price);
                record.setYuan(yuan);
                record.setType(type);
                record.setGassup(gassup);
                record.setRemark(remark);
                record.setCarId(carId);
                record.setForget(forget);
                record.setLightOn(lightOn);
                record.setStationId(stationId);
                records.add(record);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return records;
    }

    //查询当前选中车辆的最近一年的信息
    @Override
    public List<RecordEntity> querySelectedYearRecord() {
        List<RecordEntity> recordEntities = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "SELECT A.date, A.odometer, A.price, A.yuan, A.carId, datetime(A.date / 1000, 'unixepoch')" +
                " from records_tbl as A, cars_tbl as B" +
                " WHERE A.date > (" +
                " strftime('%s', datetime('now', '-12 month')) * 1000" +
                ")" +
                " AND" +
                " A.carId = B._id" +
                " AND" +
                " B.selected = 1;";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null && cursor.moveToFirst()) {
            int indexDate = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexTotal = cursor.getColumnIndex(BearSQLiteHelper.TOTAL);
            int indexCarId = cursor.getColumnIndex(BearSQLiteHelper.CARID);
            do {
                int date = cursor.getInt(indexDate);
                int odometer = cursor.getInt(indexOdometer);
                float price = cursor.getFloat(indexPrice);
                float yuan = cursor.getFloat(indexTotal);
                int carId = cursor.getInt(indexCarId);
                RecordEntity recordEntity = new RecordEntity();
                recordEntity.setDate(date);
                recordEntity.setOdometer(odometer);
                recordEntity.setPrice(price);
                recordEntity.setYuan(yuan);
                recordEntity.setCarId(carId);
                recordEntities.add(recordEntity);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return recordEntities;
    }

    //查询当前选中车辆的最近半年的信息
    @Override
    public List<RecordEntity> querySelectedHalfYearRecord() {
        List<RecordEntity> recordEntities = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "SELECT A.date, A.odometer, A.price, A.yuan, A.carId, datetime(A.date / 1000, 'unixepoch')" +
                " from records_tbl as A, cars_tbl as B" +
                " WHERE A.date > (" +
                " strftime('%s', datetime('now', '-6 month')) * 1000" +
                ")" +
                " AND" +
                " A.carId = B._id" +
                " AND" +
                " B.selected = 1;";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null && cursor.moveToFirst()) {
            int indexDate = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexTotal = cursor.getColumnIndex(BearSQLiteHelper.TOTAL);
            int indexCarId = cursor.getColumnIndex(BearSQLiteHelper.CARID);
            do {
                int date = cursor.getInt(indexDate);
                int odometer = cursor.getInt(indexOdometer);
                float price = cursor.getFloat(indexPrice);
                float yuan = cursor.getFloat(indexTotal);
                int carId = cursor.getInt(indexCarId);
                RecordEntity recordEntity = new RecordEntity();
                recordEntity.setDate(date);
                recordEntity.setOdometer(odometer);
                recordEntity.setPrice(price);
                recordEntity.setYuan(yuan);
                recordEntity.setCarId(carId);
                recordEntities.add(recordEntity);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return recordEntities;
    }

    //查询当前选中车辆的最近三个月的信息
    @Override
    public List<RecordEntity> querySelectedThreeMonthsRecord() {
        List<RecordEntity> recordEntities = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "SELECT A.date, A.odometer, A.price, A.yuan, A.carId, datetime(A.date / 1000, 'unixepoch')" +
                " from records_tbl as A, cars_tbl as B" +
                " WHERE A.date > (" +
                " strftime('%s', datetime('now', '-3 month')) * 1000" +
                ")" +
                " AND" +
                " A.carId = B._id" +
                " AND" +
                " B.selected = 1;";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null && cursor.moveToFirst()) {
            int indexDate = cursor.getColumnIndex(BearSQLiteHelper.DATE);
            int indexOdometer = cursor.getColumnIndex(BearSQLiteHelper.ODOMETER);
            int indexPrice = cursor.getColumnIndex(BearSQLiteHelper.PRICE);
            int indexTotal = cursor.getColumnIndex(BearSQLiteHelper.TOTAL);
            int indexCarId = cursor.getColumnIndex(BearSQLiteHelper.CARID);
            do {
                int date = cursor.getInt(indexDate);
                int odometer = cursor.getInt(indexOdometer);
                float price = cursor.getFloat(indexPrice);
                float yuan = cursor.getFloat(indexTotal);
                int carId = cursor.getInt(indexCarId);
                RecordEntity recordEntity = new RecordEntity();
                recordEntity.setDate(date);
                recordEntity.setOdometer(odometer);
                recordEntity.setPrice(price);
                recordEntity.setYuan(yuan);
                recordEntity.setCarId(carId);
                recordEntities.add(recordEntity);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return recordEntities;
    }

    //查询当前选中车辆的每年的加油费用
    @Override
    public List<MoneyEntity> queryEachYearMoney() {
        List<MoneyEntity> entities = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select" +
                " strftime('%Y', datetime(A.date / 1000, 'unixepoch'),'localtime') time," +
                " sum(A.yuan) money" +
                " from records_tbl as A, cars_tbl as B" +
                " where A.carId = B._id and B.selected = 1" +
                " GROUP BY time" +
                " ORDER BY time;";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null && cursor.moveToFirst()) {
            int indexTime = cursor.getColumnIndex("time");
            int indexMoney = cursor.getColumnIndex("money");
            do {
                String time = cursor.getString(indexTime);
                float money = cursor.getFloat(indexMoney);
                MoneyEntity moneyEntity = new MoneyEntity();
                moneyEntity.setTime(time);
                moneyEntity.setMoney(money);
                entities.add(moneyEntity);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return entities;
    }

    //查询当前选中车辆的每月的加油费用
    @Override
    public List<MoneyEntity> queryEachMonthMoney() {
        List<MoneyEntity> entities = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select" +
                " strftime('%Y-%m',datetime(A.date / 1000, 'unixepoch'),'localtime') time," +
                " sum(A.yuan) money" +
                " from records_tbl as A, cars_tbl as B" +
                " where A.carId = B._id and B.selected = 1" +
                " GROUP BY time" +
                " ORDER BY time;";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null && cursor.moveToFirst()) {
            int indexTime = cursor.getColumnIndex("time");
            int indexMoney = cursor.getColumnIndex("money");
            do {
                String time = cursor.getString(indexTime);
                float money = cursor.getFloat(indexMoney);
                MoneyEntity moneys = new MoneyEntity();
                moneys.setTime(time);
                moneys.setMoney(money);
                entities.add(moneys);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return entities;
    }

    //打开数据库
    private SQLiteDatabase openDatabase() {
        return mHelper.getWritableDatabase();
    }

    //关闭数据库
    private void closeDatabase(SQLiteDatabase db) {
        if (db != null) {
            db.close();
        }
    }


}
